﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using IQ.DbBase;

namespace SqlServerAdmin
{
    public class SqlDeadSp
    {
        public delegate void Progress(int position);
        public event Progress SetMaxPosition;
        public event Progress SetCurrentPosition;
		#region # Constructor # 
        public SqlDeadSp()
		{
            loadObject();
		}
		#endregion

		#region - Private variables - 
        private DataTable dtSp;
        private DataTable dtTables;
        private DataTable dtDeadTables;
        private byte minTableNameLength = 4; // take care of table aliasses
        private string[] keyWords = new string[4] { "OPEN ", "UPDATE ", "FROM ", "JOIN " };
        private string tableSubSelect = "SELECT";
        private char[] removeChars = new char[4] { '\r', '\n',';', ','};
        private string[] replaceChars = new string[2] { "(", ")" };
        private const string keyCursor = "OPEN ";
        private const char tableTemp = '#';
        private const char tableVar = '@';
        //
        private string currentSpName;
        private string currentSpText;
        private string lastMessage;
        #endregion 

		#region - Private methods - 
        private void loadObject()
        {
            dtSp = DbHelper.Load_Table(SqlQueries.SqlProcedures);
            dtTables = DbHelper.Load_Table(SqlQueries.SqlTables);
        }
        private void creatDeadTable()
        {
            dtDeadTables = new DataTable("dtDeadT");
            DbHelper.AddDataColumn(dtDeadTables, SqlQueries.Field_Name, "System.String", false, "");
            DbHelper.AddDataColumn(dtDeadTables, SqlQueries.Field_TableName, "System.String", false, "");
        }
        private string removeCommentsFromSp(string spText)
        {
            int iPos = spText.IndexOf("--");
            while (iPos >= 0)
            {
                int iTo = spText.IndexOf('\n', iPos);
                if (iTo < 0)
                    iTo = spText.Length-1;
                spText = spText.Remove(iPos, iTo - iPos + 1);
                iPos = spText.IndexOf("--");
            }
            return spText;
        }
        private string getSpText(string spName, bool removeComments)
        {
            string spText = "";
            DataTable dtText = DbHelper.Load_Table(SqlQueries.SqlProcDetail + "OBJECT_ID('" + spName + "')");
            for (int i = 0; i < dtText.Rows.Count; i++)
            {
                spText += dtText.Rows[i][0].ToString() + " ";
            }
            if (removeComments)
                return removeCommentsFromSp(spText.ToString());
            else
                return spText;
        }
        private string cleanTableName(string tableName)
        {
            for (int i=0; i < removeChars.Length; ++i)
            {
                if (tableName.Contains(removeChars[i]))
                    tableName = tableName.Remove(tableName.IndexOf(removeChars[i]));
            }
            for (int i = 0; i < replaceChars.Length; ++i)
            {
                tableName = tableName.Replace(replaceChars[i], "");
            }
            tableName = tableName.Trim();
            if (tableName.ToUpper() == tableSubSelect)
                return "";
            else
                return tableName;
        }
        private List<string> findTablesInSp(string spText)
        {
            List<string> cursors = null;
            if (spText.IndexOf(keyCursor) >= 0)
                cursors = new List<string>();
            //
            List<string> tables = new List<string>();
            for (int i = 0; i < keyWords.Length; ++i)
            {
                int iPos = spText.IndexOf(keyWords[i]);
                while (iPos >= 0)
                {
                    int iSpc = spText.IndexOf(' ', iPos + keyWords[i].Length);
                    string tableName = spText.Substring(iPos+keyWords[i].Length, spText.IndexOf(' ',iPos + keyWords[i].Length)-iPos-keyWords[i].Length);
                    if (!String.IsNullOrEmpty(tableName) &&
                        tableName[0] != tableTemp && 
                        tableName[0] != tableVar)
                    {
                        tableName = cleanTableName(tableName);
                        if (tableName.Length >= minTableNameLength)
                        {
                            if (cursors != null)
                            {
                                if (keyWords[i] == keyCursor && !cursors.Contains(tableName))
                                    cursors.Add(tableName);
                                if (!tables.Contains(tableName) && !cursors.Contains(tableName))
                                    tables.Add(tableName);
                            }
                            else if (!tables.Contains(tableName))
                                tables.Add(tableName);
                        }
                    }
                    iPos = spText.IndexOf(keyWords[i], iPos+1);
                }
            }
            return tables;            
        }
        private void checkForDeadTables(string spName, List<string> tables)
        { 
            for (int i = 0; i < tables.Count; ++i)
            {
                if (!tables[i].Contains("'") &&
                    dtTables.Select(SqlQueries.Field_Name + "='" + tables[i] + "'").FirstOrDefault() == null)
                {
                    DataRow dr = dtDeadTables.NewRow();
                    dr[SqlQueries.Field_Name] = spName;
                    dr[SqlQueries.Field_TableName] = tables[i];
                    dtDeadTables.Rows.Add(dr);
                }
            }
        }
        private void removeSpFromList(string spName)
        {
            DataRow[] drSp = dtDeadTables.Select(SqlQueries.Field_Name + "='" + spName + "'");
            foreach (DataRow dr in drSp)
                dtDeadTables.Rows.Remove(dr); 
        }
        #endregion

        #region - Protected variables -

        #endregion

        #region - Protected methods -

        #endregion

        #region - Public properties / variables -
        public DataTable StoreProcs
        {
            get { return dtSp; }
        }
        public DataTable DeadTables
        {
            get { return dtDeadTables; }
        }
        public string CurrentSpName
        {
            get { return currentSpName; }
        }
        public string CurrentSpText
        {
            get { return currentSpText; }
            set { currentSpText = value; }
        }
        public string LastMessage
        {
            get { return lastMessage; }
        }
		#endregion

		#region - Public methods - 
        public void RunProcess()
        {
            creatDeadTable();
            if (SetMaxPosition != null)
                SetMaxPosition(dtSp.Rows.Count-1);
            for (int i = 0; i < dtSp.Rows.Count; ++i)
            {
                //if (dtSp.Rows[i][SqlQueries.Field_Name].ToString() == "pcUpdateKeyInfoDataIn")
                {
                // Get Sp Text
                string spText = getSpText(dtSp.Rows[i][SqlQueries.Field_Name].ToString(), true);
                // Get table list
                List<string> tables = findTablesInSp(spText);
                // Find Dead tables
                checkForDeadTables(dtSp.Rows[i][SqlQueries.Field_Name].ToString(), tables);
                //
                }
                if (SetCurrentPosition != null)
                    SetCurrentPosition(i);
            }
        }
        public void Load_Sp_Text(string spName)
        {
            currentSpName = spName;
            currentSpText = getSpText(spName, false);
        }
        public void Delete_Sp(string spName)
        {
            DbHelper.ExecuteSql("DROP PROCEDURE " + spName);
            removeSpFromList(spName);
        }
        public bool UpdateCurrentSp()
        {
            lastMessage = "";
            string txtSql = currentSpText.Replace("CREATE PROCEDURE", "ALTER PROCEDURE");
            try
            {
                DbHelper.ExecuteSql(txtSql);
                removeSpFromList(currentSpName);
                return true;
            }
            catch (Exception err)
            {
                lastMessage = err.Message;
                return false;
            }
        }
		#endregion

    }
}
